﻿<SQLStoredProcedureSpec
  xmlns="bb_appfx_sqlstoredprocedure"
  xmlns:c="bb_appfx_commontypes"
  ID="6ED02270-7FA4-471A-9054-0BD2BF8514C6"
  Name="USR_USP_CHANGED_ADDRESSES"
  Description="Given a time, return all addresses with an important change since that time."
  Author="Joseph Styons"
  SPName="USR_USP_CHANGED_ADDRESSES"
  >
  <CreateProcedureSQL>
    <![CDATA[
create procedure dbo.USR_USP_CHANGED_ADDRESSES
(
  @SINCE datetime
)
as
begin
  select distinct id, 'New'               --inserts
  from address ad
  where ad.DATEADDED > @since

  union

  select distinct id, 'Changed'           --updates
  from address a
  where a.DATECHANGED > @since

  union

  select distinct aa.auditrecordid, 'Deleted'           --deletes
  from
   ADDRESSAUDIT aa
   left join address a on a.id = aa.auditrecordid
  where aa.AUDITDATE > @since                         
    and aa.AUDITTYPECODE = 2                  --"2" == "BEFORE DELETE"
end
]]>
  </CreateProcedureSQL>
</SQLStoredProcedureSpec>
